drop PROCEDURE if exists create_gauss_table_info_view_p;
-- 创建存储过程
CREATE OR REPLACE PROCEDURE create_gauss_table_info_view_p (
    view_name_i TEXT,  -- 创建的视图名，格式：tab_gs_ca_prac2_v
    host_i TEXT,  -- 数据链中的主机名
    dbname_i TEXT,  -- 数据链中的数据库名
    port_i TEXT,  -- 数据链中的端口
    user_i TEXT,  -- 数据链中的用户名
    password_i TEXT,  -- 数据链中的密码
    source_i TEXT,  -- 来源，例如：gs_ca_prac2
    table_schema_i TEXT  -- schema
)
LANGUAGE plpgsql
AS $$
DECLARE
    dynamic_sql TEXT;         -- 动态 SQL 语句
    sql_safe BOOLEAN := true; -- SQL 注入检测标志
BEGIN
    -- 1. 参数安全验证
    -- 检查表名和列名是否符合标识符规范
--     IF target_table !~ '^[a-zA-Z_][a-zA-Z0-9_]*$' THEN
--         RAISE EXCEPTION 'Invalid table name: %', target_table;
--     END IF;
--     
--     IF filter_column !~ '^[a-zA-Z_][a-zA-Z0-9_]*$' THEN
--         RAISE EXCEPTION 'Invalid column name: %', filter_column;
--     END IF;
    
    -- 2. 构建动态 SQL（使用格式化和参数化）
--     dynamic_sql := format(
--         'SELECT * FROM %I WHERE %I = $ 1',  -- %I 自动处理标识符引号
--         target_table, 
--         filter_column
--     );
    dynamic_sql := 
'
drop view if exists ' || view_name_i || ';
create view         ' || view_name_i || ' as 
select * 
  from dblink(''host=' || host_i || ' dbname=' || dbname_i || ' port=' || port_i || ' user=' || user_i || ' password=' || password_i || ''',
''
select ''''' || source_i || ''''' as source
      ,(case
         when substr(version(), 0, 1) = ''''(''''
           then substring(substr(version(), 2, length(version())) from ''''^[^ ]+'''')
         else substring(version() from ''''^[^ ]+'''')
       end)::text as database_type
   ,ic.table_schema::text as schema
      ,o.owner::text
   ,ic.table_name::text
      ,tab_com.description::text as table_comment
      ,ic.ordinal_position::text as column_num
      ,ic.column_name::text
      ,(case
         when ic.data_type = ''''bigint''''
           then ''''INT8''''
     when ic.data_type = ''''integer''''
       then ''''INT4''''
     when ic.data_type = ''''smallint''''
       then ''''INT2''''
         when ic.data_type in (''''nvarchar2'''') AND ic.character_maximum_length IS NOT NULL
           then upper(ic.data_type) || ''''('''' || ic.character_maximum_length || '''')''''
     when ic.data_type in (''''nvarchar2'''') AND ic.character_maximum_length IS NULL
           then ''''NVARCHAR2''''
         when ic.data_type in (''''character varying'''')
           then ''''VARCHAR('''' || ic.character_maximum_length || '''')''''
      when ic.data_type in (''''character'''')
       then ''''CHAR('''' || ic.character_maximum_length || '''')''''
         when ic.data_type = ''''timestamp without time zone''''
           then ''''TIMESTAMP('''' || ic.datetime_precision || '''')''''
         when ic.data_type = ''''time without time zone''''
           then ''''TIME('''' || ic.datetime_precision || '''')''''
         when ic.data_type = ''''numeric'''' and ic.numeric_precision is not null and ic.numeric_scale is not null
           then ''''NUMERIC('''' || ic.numeric_precision || '''','''' || ic.numeric_scale || '''')''''
     when ic.data_type = ''''numeric'''' and ic.numeric_precision is null and ic.numeric_scale is null
           then ''''NUMERIC''''
         when ic.data_type = ''''text''''
           then ''''TEXT''''
         when ic.data_type = ''''date''''
           then ''''DATE''''
         else upper(ic.data_type)
       end)::text as data_type
      ,col_com.description::text as column_comment
      ,(CASE
         when (POSITION(lower(''''::regclass'''') IN lower(ic.column_default)) > 0)
           then REPLACE(ic.column_default, ''''::regclass'''', '''''''')
         when (POSITION(lower(''''::integer'''') IN lower(ic.column_default)) > 0)
           then REPLACE(REPLACE(ic.column_default, ''''::integer'''', ''''''''), '''''''''''''''', '''''''')
         else ic.column_default
       end)::text as column_default
      ,ic.is_nullable::text
      ,(case
         when pc.conname is null then null::text
         else ''''Y''''
       end)::text as pkey
 from pg_class c
  join adm_objects o 
   on o.object_id = c.oid and o.object_type like ''''%table%''''
  and c.relkind in (''''r'''',''''p'''')
  join pg_namespace n
    on c.relnamespace = n.oid
  join information_schema.columns ic
   on ic.table_name = c.relname
  and n.nspname = ic.table_schema
  left join pg_description tab_com
    on tab_com.objoid = c.oid
   and tab_com.objsubid = 0
  left join pg_description col_com
    on col_com.objoid = c.oid
   and col_com.objsubid = ic.ordinal_position
  left join (
    SELECT conname, conrelid , unnest(conkey) as column_num
      FROM pg_constraint
     where contype = ''''p'''') as pc
    on pc.conrelid = c.oid
   and pc.column_num = ic.ordinal_position
 where 1=1
   and ic.table_schema in (
      ''''' || table_schema_i || '''''
  )
 order by ic.table_schema, ic.table_name, ic.ordinal_position
'')
as t (
  source text,
  database_type text,
  schema text,
  owner text,
  table_name text,
  table_comment text,
  column_num text,
  column_name text,
  data_type text,
  column_comment text,
  column_default text,
  is_nullable text,
  pkey text
)
';

    RAISE NOTICE 'dynamic_sql=%', dynamic_sql;
    
    -- 3. 执行动态 SQL（使用 USING 传递值参数）
    --RAISE NOTICE 'Executing: %', dynamic_sql;
--     EXECUTE dynamic_sql USING filter_value;
    EXECUTE dynamic_sql;
    
    -- 4. 结果处理（此处为示例，实际根据需求修改）
    RAISE NOTICE 'Operation completed successfully';
EXCEPTION
    WHEN others THEN
        RAISE EXCEPTION 'Dynamic SQL execution failed: %', SQLERRM;
END;
$$;
